Amazon Athena で Amazon S3 のサーバーアクセスログをクエリしてみた

m.hayakawa

2024.07.01

はじめに

猫とアポロチョコが好きな、アノテーション株式会社の m.hayakawa です。

本記事はAWS CLI を用いて S3 サーバーアクセスログを有効化してみた | DevelopersIOの続きの記事となります。

S3 サーバーアクセスログに関する検証を行うことが多いのですが、Athena でクエリをする際に毎回やり方を忘れてしまうため、手順を記事化します。

同じことに困っている方の一助になれば幸いです。

前提条件

ログ保存先の S3 バケット：logging-bucket-test0123

やってみた

クエリの結果の場所を指定

クエリエディタを開きます。

下記の画面が出た場合、クエリの結果の場所を指定されていないため、設定が必要です。

設定を編集 ボタンを押下し、Location of query result に任意の S3 バケットを指定し、保存ボタン を押下します。

Athena のデータベースとテーブルを作成する

データベースを作成するために、下記のクエリを実行します。

create database s3_access_logs_db

続いて、テーブルを作成するために、下記のクエリを実行します。

CREATE EXTERNAL TABLE `s3_access_logs_db.mybucket_logs`(
  `bucketowner` STRING,
  `bucket_name` STRING,
  `requestdatetime` STRING,
  `remoteip` STRING,
  `requester` STRING,
  `requestid` STRING,
  `operation` STRING,
  `key` STRING,
  `request_uri` STRING,
  `httpstatus` STRING,
  `errorcode` STRING,
  `bytessent` BIGINT,
  `objectsize` BIGINT,
  `totaltime` STRING,
  `turnaroundtime` STRING,
  `referrer` STRING,
  `useragent` STRING,
  `versionid` STRING,
  `hostid` STRING,
  `sigv` STRING,
  `ciphersuite` STRING,
  `authtype` STRING,
  `endpoint` STRING,
  `tlsversion` STRING)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://logging-bucket-test0123/source-bucket-test0123/'

ちなみに、データベース名とテーブル名を ` で囲まないと下記のエラーが出てハマります。私はハマりました。

line 1:8: mismatched input 'EXTERNAL'. Expecting: 'MATERIALIZED', 'MULTI', 'OR', 'PROTECTED', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'

クエリを投げてみる

ひとまず直近の 10 件のログを見てみます。

SELECT * FROM "logging-bucket-test0123_logs" limit 10;

"bucketowner","bucket_name","requestdatetime","remoteip","requester","requestid","operation","key","request_uri","httpstatus","errorcode","bytessent","objectsize","totaltime","turnaroundtime","referrer","useragent","versionid","hostid","sigv","ciphersuite","authtype","endpoint","tlsversion"
"MASK","source-bucket-test0123","30/Jun/2024:04:17:24 +0000","-","arn:aws:sts::<AccountID>:assumed-role/AWSServiceRoleForTrustedAdvisor/TrustedAdvisor_<AccountID>_698bb03f-6b68-4ffd-abac-c320e7a80f28","CMPXWYFZNRSAMPLE","REST.GET.LOCATION","-","""GET /source-bucket-test0123?location HTTP/1.1""","200","-","142",,"4","-","""-""","""-""","-","Masked","SigV4","TLS_AES_128_GCM_SHA256","AuthHeader","s3.ca-central-1.amazonaws.com","TLSv1.3"
<後略>

Trusted Advisor から何かアクセスされていることが確認できました。オブジェクトを S3 に配置するだけでも、AWS サービス上から何かしらのアクセスは発生しているようです。

前回のブログでオブジェクトをバケットへコピーしたので、その記録を確認してみます。operation が REST.PUT.OBJECT である記録をクエリします。

SELECT * FROM "mybucket_logs" 
WHERE operation='REST.PUT.OBJECT'

"bucketowner","bucket_name","requestdatetime","remoteip","requester","requestid","operation","key","request_uri","httpstatus","errorcode","bytessent","objectsize","totaltime","turnaroundtime","referrer","useragent","versionid","hostid","sigv","ciphersuite","authtype","endpoint","tlsversion"
"MASKED","source-bucket-test0123","25/Jun/2024:02:40:06 +0000","<IP Address>","arn:aws:sts::<AccountID>:assumed-role/<IAMRole>/<SessionName>","91J7WDK3KQSAMPLE","REST.PUT.OBJECT","sample.txt","""PUT /sample.txt HTTP/1.1""","200","-",,"6","40","20","""-""","""aws-cli/2.16.11 md/awscrt#0.20.11 ua/2.0 os/linux#6.1.92-99.174.amzn2023.x86_64 md/arch#x86_64 lang/python#3.11.8 md/pyimpl#CPython exec-env/CloudShell cfg/retry-mode#standard md/installer#exe md/distrib#amzn.2023 md/prompt#off md/command#s3.cp""","-","MASKED","SigV4","TLS_AES_128_GCM_SHA256","AuthHeader","source-bucket-test0123.s3.ap-northeast-1.amazonaws.com","TLSv1.3"

当該の記録を確認できました！